﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class PhotosDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoPhotos model)
        {
            model.PHO_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Photos]");
            sql.Append("([PHO_ID],[PHO_TITLE],[PHO_PROCEDURES],[PHO_URL],[PHO_DATE],[PHO_IsRound])");
            sql.Append("VALUES");
            sql.Append("(@PHO_ID,@PHO_TITLE,@PHO_PROCEDURES,@PHO_URL,@PHO_DATE,@PHO_IsRound)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoPhotos model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Photos] SET ");
            sql.Append("PHO_TITLE=@PHO_TITLE");
            sql.Append(",PHO_PROCEDURES=@PHO_PROCEDURES");
            sql.Append(",PHO_URL=@PHO_URL");
            sql.Append(",PHO_DATE=@PHO_DATE");
            sql.Append(",PHO_IsRound=@PHO_IsRound");
            sql.Append(" WHERE ");
            sql.Append("PHO_ID=@PHO_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string pHO_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("DELETE FROM Photos");
            sql.Append(" WHERE ");
            sql.Append("PHO_ID=@PHO_ID and PHO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PHO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pHO_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
       
        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns></returns>
        public BoPhotos GetModel(string pHO_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PHO_ID,PHO_TITLE,PHO_PROCEDURES,PHO_URL,PHO_DATE,PHO_IsRound  FROM  Photos");
            sql.Append(" WHERE ");
            sql.Append("PHO_ID=@PHO_ID and PHO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PHO_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = pHO_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoPhotos> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoPhotos> GetContestPhotos(string contestId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PHO_ID,PHO_TITLE,PHO_PROCEDURES,PHO_URL,PHO_DATE,PHO_IsRound ");
            sql.Append(" FROM Photos AS P INNER JOIN Procedures AS R ON P.PHO_PROCEDURES=R.PRO_ID ");
            sql.Append(" WHERE R.PRO_CONTESTS=@PRO_CONTESTS and PHO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PRO_CONTESTS", SqlDbType.NVarChar, 50);
            parameter[0].Value = contestId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoPhotos> list = GetList(table);
            return list;
        }

        public IList<BoPhotos> GetProcedurePhotos(string procId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PHO_ID,PHO_TITLE,PHO_PROCEDURES,PHO_URL,PHO_DATE,PHO_IsRound ");
            sql.Append(" FROM Photos ");
            sql.Append(" WHERE PHO_PROCEDURES=@PHO_PROCEDURES and PHO_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@PHO_PROCEDURES", SqlDbType.NVarChar, 50);
            parameter[0].Value = procId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoPhotos> list = GetList(table);
            return list;
        }

        public IList<BoPhotos> GetAllPhotos()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PHO_ID,PHO_TITLE,PHO_PROCEDURES,PHO_URL,PHO_DATE,PHO_IsRound FROM Photos where PHO_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoPhotos> list = GetList(table);
            return list;
        }

        /// <summary>
        /// 得到轮显图片列表
        /// </summary>
        /// <returns>返回轮显图片列表</returns>
        public IList<BoPhotos> GetRoundPhoto()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT PHO_ID,PHO_TITLE,PHO_PROCEDURES,PHO_URL,PHO_DATE,PHO_IsRound FROM Photos where PHO_IsShow='true' and PHO_IsRound='true' ");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoPhotos> list = GetList(table);
            return list;
        }

          /// <summary>
        /// 得到轮显的个数
        /// </summary>
        /// <returns>返回轮显的个数</returns>
        public int GetIsRound()
        {
            string sql = "select count(*) from Photos where PHO_IsShow='true' and PHO_IsRound='true' ";
            return (int)dbhelper.ExcuteQuery(sql).Rows[0][0];
        }

        private IList<BoPhotos> GetList(DataTable table)
        {
            List<BoPhotos> list = new List<BoPhotos>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoPhotos model = new BoPhotos();
                    model.PHO_ID = (string)table.Rows[i]["PHO_ID"];
                    model.PHO_TITLE = (string)table.Rows[i]["PHO_TITLE"];
                    model.PHO_PROCEDURES = (string)table.Rows[i]["PHO_PROCEDURES"];
                    model.PHO_URL = (string)table.Rows[i]["PHO_URL"];
                    model.PHO_DATE = (DateTime)table.Rows[i]["PHO_DATE"];
                    model.PHO_IsRound = (bool)table.Rows[i]["PHO_IsRound"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoPhotos model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@PHO_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@PHO_TITLE",SqlDbType.NVarChar,50),
                new SqlParameter("@PHO_PROCEDURES",SqlDbType.NVarChar,50),
                new SqlParameter("@PHO_URL",SqlDbType.NVarChar,255),
                new SqlParameter("@PHO_DATE",SqlDbType.DateTime,8),
                new SqlParameter("@PHO_IsRound",SqlDbType.Bit),
            };
            parameters[0].Value = model.PHO_ID;
            parameters[1].Value = model.PHO_TITLE;
            parameters[2].Value = model.PHO_PROCEDURES;
            parameters[3].Value = model.PHO_URL;
            parameters[4].Value = model.PHO_DATE;
            parameters[5].Value = model.PHO_IsRound;
            return parameters;
        }
    }
}